/**
 * 
 */
package rentReturnService;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javafx.beans.property.SimpleIntegerProperty;
import rentReturnEntity.CarDisplayableEntity;
import rentReturnEntity.TruckDisplayableEntity;
import constants.Constants;

/**
 * @author Sashiraj Dhanarajan
 *
 */
public class ReportService {

	private Connection connection;
	private PreparedStatement statement;
	/**
	 * This method established database connection
	 * @pre Network should be connected
	 * @post Connected with the database
	 */
	public ReportService(){
		try {
			Class.forName(Constants.JDBC_DRIVER);
			connection = DriverManager.getConnection(Constants.DB_URL,Constants.USER_NAME,Constants.PWD);
		} 
		catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
	}

	/**
	 * This method fetches the report of rented vehicles from Rent table
	 * @return
	 * @throws SQLException 
	 * @pre The vehicled should be rented on the current day
	 * @post selects the number of vehicles rented by grouping the vehicle type
	 */
	public ResultSet fetchRentReport() throws SQLException{
		ResultSet rs = null;
		String query = "select v.vehicleType,count(v.vehicleType) as numberofVehicles "
				+ "from ((vehicle v inner join rent r on v.vehicleId = r.vehicleId ) "
				+ "inner join rental_vehicle rv on r.vehicleId = rv.vehicleId and"
				+ " r.rentDate = curdate() and rv.status = 'rented') GROUP BY v.vehicleType";
		try {
			statement = connection.prepareStatement(query);
			rs =  statement.executeQuery();
		} 
		catch (SQLException e1) {
			e1.printStackTrace();
			throw e1;
		}
		return rs;
	}

	/**
	 * This method fetches the report of returned vehicles from Return table
	 * @return
	 * @throws SQLException 
	 * @pre The vehicled should be returned on the current day
	 * @post selects the number of vehicles rented by grouping the vehicle type
	 */
	public ResultSet fetchReturnReport() throws SQLException{
		ResultSet rs = null;
		String query = "select v.vehicleType,count(v.vehicleType) as numberofVehicles "
				+ "from ((vehicle v inner join rent r on v.vehicleId = r.vehicleId ) "
				+ "inner join return_vehicle rv on r.rentId = rv.returnId and"
				+ " rv.returnDate = curdate()) GROUP BY v.vehicleType";
		try{
			statement = connection.prepareStatement(query);
			rs = statement.executeQuery();
		}
		catch (SQLException e1){
			e1.printStackTrace();
			throw e1;
		}

		return rs;
	}


public TruckDisplayableEntity fetchTruckOverallVehicleList(String status) throws SQLException
{
	ResultSet rs =null;
	TruckDisplayableEntity truck = new TruckDisplayableEntity();
	String query =null;
	if(status.equalsIgnoreCase("Purchased"))
		query = "select count(*) from purchased_vehicle pv join vehicle v on v.vehicleId=pv.vehicleId where v.vehicleId not in (select vehicleID from rental_vehicle) and v.vehicleID not in (select salevehicleID from sale_vehicle) and vehicleClass=? and vehicleType=?";
		else
			if(status.equalsIgnoreCase("Rented"))
				query ="select count(v.vehicleid) from rental_vehicle rv join vehicle v on v.vehicleId=rv.vehicleId and vehicleClass=? and vehicleType=?";
			else if(status.equalsIgnoreCase("Sale"))
				query="select count(v.vehicleid) from sale_vehicle sv join vehicle v on v.vehicleId=sv.saleVehicleId and vehicleClass=? and vehicleType=?";
		try{
			statement = connection.prepareStatement(query);
			statement.setString(1, "Truck");
			statement.setString(2, "Box Trucks");
			rs = statement.executeQuery();
			if(rs.next())
				truck.setBoxTrucks1(new SimpleIntegerProperty(rs.getInt(1)));
			
			statement.setString(2, "Cargo Vans");
			rs = statement.executeQuery();
			if(rs.next())
				truck.setCargoVan1(new SimpleIntegerProperty(rs.getInt(1)));
				
			statement.setString(2, "12-Foot");
			rs = statement.executeQuery();
			if(rs.next())
				truck.setFoot12long(new SimpleIntegerProperty(rs.getInt(1)));
				
			statement.setString(2, "15-Foot");
			rs = statement.executeQuery();
			if(rs.next())
			truck.setFoot15long(new SimpleIntegerProperty(rs.getInt(1)));	
				
			statement.setString(2, "24-Foot");
			rs = statement.executeQuery();
			if(rs.next())
				truck.setFoot24long(new SimpleIntegerProperty(rs.getInt(1)));
				
			int total = truck.getBoxTrucks1() + truck.getCargoVan1() + truck.getFoot12long() + truck.getFoot15long() + truck.getFoot24long();
			truck.setTruckTotal11(new SimpleIntegerProperty(total));
			
		}
		catch (SQLException e1){
			e1.printStackTrace();
			throw e1;
		}
		
			
	return truck;
}
	
public CarDisplayableEntity fetchCarOverallVehicleList(String status) throws SQLException
{
	ResultSet rs = null;
	CarDisplayableEntity carDisplay = new CarDisplayableEntity();
	String query=null;
	if(status.equalsIgnoreCase("Purchased"))
	query = "select count(*) from purchased_vehicle pv join vehicle v on v.vehicleId=pv.vehicleId where v.vehicleId not in (select vehicleID from rental_vehicle) and v.vehicleID not in (select salevehicleID from sale_vehicle) and vehicleClass=? and vehicleType=?";
	else
		if(status.equalsIgnoreCase("Rented"))
			query ="select count(v.vehicleid) from rental_vehicle rv join vehicle v on v.vehicleId=rv.vehicleId and vehicleClass=? and vehicleType=?";
		else if(status.equalsIgnoreCase("Sale"))
			query="select count(v.vehicleid) from sale_vehicle sv join vehicle v on v.vehicleId=sv.saleVehicleId and vehicleClass=? and vehicleType=?";
	try{
		
		statement = connection.prepareStatement(query);
		statement.setString(1, "Car");
		statement.setString(2, "Economy");
		rs = statement.executeQuery();
		if(rs.next())
			carDisplay.setEconomy1(new SimpleIntegerProperty(rs.getInt(1)));
		
		
		statement.setString(2, "Premium");
		rs = statement.executeQuery();
		if(rs.next())
			carDisplay.setPremium1(new SimpleIntegerProperty(rs.getInt(1)));
		
		
		statement.setString(2, "Luxury");
		rs = statement.executeQuery();
		if(rs.next())
			carDisplay.setLuxury1(new SimpleIntegerProperty(rs.getInt(1)));
		
		statement.setString(2, "Compact");
		rs = statement.executeQuery();
		if(rs.next())
			carDisplay.setCompact1(new SimpleIntegerProperty(rs.getInt(1)));
		
		statement.setString(2, "Mid-Size");
		rs = statement.executeQuery();
		if(rs.next())
			carDisplay.setMidSize1(new SimpleIntegerProperty(rs.getInt(1)));

		
		statement.setString(2, "Standard");
		rs = statement.executeQuery();
		if(rs.next())
			carDisplay.setStandard1(new SimpleIntegerProperty(rs.getInt(1)));
		
			
		statement.setString(2, "SUV");
		rs = statement.executeQuery();
		if(rs.next())
		carDisplay.setSuv1(new SimpleIntegerProperty(rs.getInt(1)));
		
		
		statement.setString(2, "VAN");
		rs = statement.executeQuery();
		if(rs.next())
			carDisplay.setVan1(new SimpleIntegerProperty(rs.getInt(1)));
		
		int total = carDisplay.getCompact1()+carDisplay.getEconomy1()+carDisplay.getVan1()+carDisplay.getLuxury1()+carDisplay.getMidSize1()+carDisplay.getPremium1()+carDisplay.getStandard1()+carDisplay.getSuv1();
		carDisplay.setCarTotal11(new SimpleIntegerProperty(total));
		}
	
	catch (SQLException e1){
		e1.printStackTrace();
		throw e1;
	}
	
	return carDisplay;
}
	
	
	public void closeConnection(){
		try{
			if(statement != null){
				statement.close();
			}
			if(connection != null){
				connection.close();
			}
		}
		catch(SQLException sqlExec){
			sqlExec.printStackTrace();
		}
	}
}
